package com.gjm.tool.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class TableSqlTest {




    public static void main(String[] args) throws Exception{
        /**
         * 1. 通过sql，查询所有涉及的表、字段等信息，导出excel
         * 2. 通过代码，完成excel读取，然后按照 模型控制变更表要求，完成excel的输入，可以是半成品
         * 3. 在半成品的基础上，通过复制（代码生产的excel内容），完成控制模型变更表的合成
         */
        List<List<Object>> fieldList = oracleTable();

        List<FieldVO> list = new ArrayList<>();

        for (List<Object> lines : fieldList) {
            if (CollectionUtil.isEmpty(lines) || lines.size() < 8){
                continue;
            }
            FieldVO fieldVO = new FieldVO(
                    String.valueOf(lines.get(0)),
                    String.valueOf(lines.get(1)),
                    String.valueOf(lines.get(2)),
                    String.valueOf(lines.get(3)),
                    String.valueOf(lines.get(4)),
                    String.valueOf(lines.get(5)),
                    String.valueOf(lines.get(6)),
                    String.valueOf(lines.get(7))
            );
            list.add(fieldVO);
        }

        saveToExcel(list);

    }


    public static void saveToExcel(List<FieldVO> fieldList) {
        ExcelWriter writer = ExcelUtil.getWriter("C:\\Users\\Administrator\\Desktop\\allTable1.xlsx");
        List<List<?>> rows = new ArrayList<>();
        List<?> row1 = CollUtil.newArrayList("字段名称", "字段注释", "字典类型", "字典长度", "是否为空", "表名");
        Map<String, List<FieldVO>> tableMap = fieldList.stream().collect(Collectors.groupingBy(i -> i.getTABLE_NAME() + "--" + i.getTABLE_COMMENTS()));
        rows.add(row1);
        for (Map.Entry<String, List<FieldVO>> entry : tableMap.entrySet()) {
            for (FieldVO fieldVO : entry.getValue()) {
                rows.add(CollUtil.newArrayList(fieldVO.getCOLUMN_NAME(), fieldVO.getCOMMENTS(), fieldVO.getDATA_TYPE(), fieldVO.getDATA_LENGTH(), fieldVO.getNULLABLE(), entry.getKey()));
            }
            rows.add(CollUtil.newArrayList("", "", "", "", "", ""));
        }
        writer.write(rows, true);
        writer.close();
    }


    public static List<List<Object>> oracleTable() {
        String filePath = "C:\\Users\\Administrator\\Desktop\\pre.xlsx";
        ExcelReader reader = ExcelUtil.getReader(filePath, 0);
        // 读取指定sheet页面，0表示第一个sheet
        List<List<Object>> sheetList = reader.read(0);
        // 关闭读取器，释放资源
        reader.close();
        return sheetList.subList(2, sheetList.size());
    }

    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    static class FieldVO {
        private String TABLE_NAME;
        private String TABLE_COMMENTS;
        private String COLUMN_NAME;
        private String COMMENTS;
        private String PRE_COMMENTS;
        private String DATA_TYPE;
        private String DATA_LENGTH;
        private String NULLABLE;
    }
}
